package com.hulu.yec.teach.testpaper.service;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.hulu.yec.common.Constant;
import com.hulu.yec.common.PageModel;
import com.hulu.yec.common.exception.DaoRuntimeException;
import com.hulu.yec.common.util.DBUtil;
import com.hulu.yec.common.util.LogUtil;
import com.hulu.yec.teach.testpaper.entity.PaperConditionEntity;
import com.hulu.yec.teach.testpaper.entity.TestPaperEntity;

public class TestPeperDao {
	/**
	 * 查询
	 * 
	 * @param pageSize
	 * @param pageNo
	 * @return PageModle
	 * @throws SQLException
	 */

	public PageModel<TestPaperEntity> list(PaperConditionEntity conditionEntity, int pageSize, int pageNo) {
		List<Object> param = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT p.id, p.name, ");
		sb.append("c.name AS 'courseName', ");
		sb.append("p.total_point AS 'totalPoint', ");
		sb.append("p.pass_point AS 'passPoint',");
		sb.append("p.total_minutes AS 'totalMinutes', ");
		sb.append("p.state ");
		sb.append("FROM qes_exam_paper p ,sys_course c " );
		sb.append("WHERE p.course_id = c.id " );
		if(StringUtils.isNoneBlank(conditionEntity.getName())){
			sb.append("AND p.name  LIKE '%"+ conditionEntity.getName()+"%' " );
		}
		if(StringUtils.isNoneBlank(conditionEntity.getCourseName())){
			sb.append(" AND c.name  = ? " );
			param.add(conditionEntity.getCourseName());
		}
		
		if(conditionEntity.getState() != 0){
			sb.append(" AND p.state=? " );
			param.add(conditionEntity.getState());
		}
		sb.append(" ORDER BY p.state=1 DESC ");
		sb.append(" LIMIT ?,?" );
		LogUtil.i(Constant.LOGGER, param.toString());
		param.add((pageNo - 1) * pageSize);
		param.add(pageSize);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
				for(int i =0;i<param.size();i++){
					preparedStatement.setObject(i+1, param.get(i));
				}
			resultSet = preparedStatement.executeQuery();
			List<TestPaperEntity> paramList = new ArrayList<>();
			while (resultSet.next()) {
				TestPaperEntity testPaper = new TestPaperEntity();
				testPaper.setId(resultSet.getInt("id"));
				testPaper.setName(resultSet.getString("name"));
				testPaper.setCourseName(resultSet.getString("courseName"));
				testPaper.setTotalPoint(resultSet.getInt("totalPoint"));
				testPaper.setPassPoint(resultSet.getInt("passPoint"));
				testPaper.setTotalMinutes(resultSet.getInt("totalMinutes"));
				testPaper.setState(resultSet.getInt("state"));
				paramList.add(testPaper);
			}
			return new PageModel<TestPaperEntity>(paramList, pageNo, pageSize, getAllRecords(conditionEntity));
		} catch (SQLException e) {
			throw new DaoRuntimeException("sql错误");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}
	/**

	 * 查询总记录数

	 * @param conditionEntity

	 * @return 

	 */
	private int getAllRecords(PaperConditionEntity conditionEntity) {
		int allRecords=0;
		List<Object> paramList = new ArrayList<>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(*) ");
		sb.append("FROM qes_exam_paper p ,sys_course c " );
		sb.append("WHERE p.course_id = c.id " );
		if(StringUtils.isNoneBlank(conditionEntity.getName())){
			sb.append("AND p.name  LIKE '%"+ conditionEntity.getName()+"%' " );
		}
		if(StringUtils.isNoneBlank(conditionEntity.getCourseName())){
			sb.append(" AND c.name  = ? " );
			paramList.add(conditionEntity.getCourseName());
		}
		
		if(conditionEntity.getState() != 0){
			sb.append(" AND p.state=? " );
			paramList.add(conditionEntity.getState());
		}
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			 if(paramList != null){
			 for (int i = 0; i < paramList.size(); i++) {
			 preparedStatement.setObject(i + 1, paramList.get(i));
			  }
			 }
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				allRecords = resultSet.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据库查询失败");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		System.out.println("所有记录数"+allRecords);
		return allRecords;
	}
	
	
	/**

	 * 查询所有的课程，返回一个课程的list

	 * 

	 * @return

	 */
	public List<String> getAllCourseName(){
		String sql="SELECT name FROM sys_course";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<String> list = new ArrayList<String>();
		try{
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				list.add(resultSet.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	//	LogUtil.i(Constant.LOGGER, list.toString());


		return list;
	}
	/**

	 * 获取Id

	 * @param id

	 * @return

	 */
	public int getState(int id) {
		String sql = "SELECT state FROM qes_exam_paper WHERE id =" + id;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt("state");
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}
	/**

	 * 状态更新

	 * @param id

	 * @param reState

	 */
	public int updateState(int id, int reState) {
		String sql = "UPDATE qes_exam_paper SET state =" + reState
				+ "  WHERE id=" + id;
		LogUtil.i(Constant.LOGGER, "sql=" + sql);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeUpdate(sql);
		} catch (SQLException e) {
			throw new RuntimeException("改角色出现sql异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
	}

}